Executing SQL Queries Using Statement in Java JDBC

Course: Advanced Java Training

Topic: Executing SQL Queries Using Statement in Java JDBC

Course Link: Advanced Java Training Course – JustAcademy

Demo Link: Register for a Free Course Demo

Website: JustAcademy Official Website

1. Introduction to Executing SQL Queries Using Statement

Java Database Connectivity (JDBC) is an API that allows Java applications to connect to relational databases and execute SQL statements. It provides interfaces and classes for establishing database connections, executing queries, processing results, and managing database operations.

The Statement interface is one of the fundamental JDBC interfaces used to execute static SQL statements against a database. It is useful when the SQL query is constructed as a complete string and does not require parameter placeholders.

For example, a Java application can use a Statement object to retrieve student records, insert a new employee, update product prices, or delete obsolete records from a database.

2. Learning Objectives

• Understand the purpose of the JDBC Statement interface.
• Learn how to create a Statement object using a database connection.
• Execute SELECT queries and process the returned ResultSet.
• Execute INSERT, UPDATE, and DELETE operations.
• Understand the differences between executeQuery(), executeUpdate(), and execute().
• Handle SQL exceptions and close JDBC resources safely.
• Identify the limitations and security risks of Statement.
• Apply JDBC Statement operations in practical Java applications.

3. What Is the Statement Interface?

The Statement interface belongs to the java.sql package. It provides methods for sending SQL statements to a database through an established JDBC connection.

A Statement object does not normally use parameter placeholders that are bound separately. Instead, the SQL command is supplied as a string when an execution method is called.

Basic Syntax

Connection connection = DriverManager.getConnection(
    url, username, password
);

Statement statement = connection.createStatement();

ResultSet resultSet = statement.executeQuery(
    "SELECT * FROM students"
);

In this example, the connection creates a Statement object, which executes a SELECT query. The database returns the matching records through a ResultSet.

Important JDBC Components

4. Types of SQL Statements Used with JDBC

JDBC Statement objects can execute different categories of SQL commands. The appropriate execution method depends on the type of command and the result expected.

The exact behavior and returned values can depend on the JDBC driver and database. For ordinary SELECT queries, use executeQuery(). For statements that do not return a ResultSet, executeUpdate() is generally appropriate.

5. Establishing a Database Connection

Before executing a SQL query, the Java application must establish a connection to the database. The following example uses MySQL as the database system.

Example Database Table

CREATE DATABASE college_db;

USE college_db;

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    course VARCHAR(100),
    marks DECIMAL(5,2)
);

INSERT INTO students (name, course, marks)
VALUES
('Amit', 'Java', 85.50),
('Priya', 'Python', 91.00),
('Rahul', 'SQL', 78.00);

Ensure that MySQL is running and that the database and table have been created. Add the appropriate MySQL Connector/J dependency to the Java project before running the JDBC examples.

Connection Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DatabaseConnectionExample {
    public static void main(String[] args) {
        String url =
            "jdbc:mysql://localhost:3306/college_db";
        String username = "your_username";
        String password = "your_password";

        try (Connection connection =
                 DriverManager.getConnection(
                     url, username, password)) {
            System.out.println(
                "Database connected successfully."
            );
        } catch (SQLException e) {
            System.out.println(
                "Database connection failed: "
                + e.getMessage()
            );
        }
    }
}

Replace the username and password with valid database credentials. In production applications, keep credentials outside source code and use appropriate secrets management.

6. Creating a Statement Object

Once a database connection has been established, the createStatement() method creates a Statement object. This object can then execute SQL commands.

Syntax

Statement statement = connection.createStatement();

Example

try (Connection connection =
         DriverManager.getConnection(
             url, username, password);
     Statement statement =
         connection.createStatement()) {

    System.out.println("Statement object created.");

} catch (SQLException e) {
    e.printStackTrace();
}

Try-with-resources automatically closes the connection and statement when execution leaves the block, including when an exception occurs.

7. Executing SELECT Queries Using executeQuery()

The executeQuery() method executes a SQL query that returns a ResultSet. It is commonly used to retrieve records from a database.

Syntax

ResultSet resultSet =
    statement.executeQuery("SELECT * FROM students");

Complete Example: Displaying All Students

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SelectStudentsExample {
    public static void main(String[] args) {
        String url =
            "jdbc:mysql://localhost:3306/college_db";
        String username = "your_username";
        String password = "your_password";

        String sql =
            "SELECT id, name, course, marks FROM students";

        try (Connection connection =
                 DriverManager.getConnection(
                     url, username, password);
             Statement statement =
                 connection.createStatement();
             ResultSet resultSet =
                 statement.executeQuery(sql)) {

            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String course =
                    resultSet.getString("course");
                double marks =
                    resultSet.getDouble("marks");

                System.out.println(
                    id + " | " + name + " | "
                    + course + " | " + marks
                );
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Understanding the Code

• createStatement() creates a Statement object.
• executeQuery(sql) sends the SELECT statement to the database.
• ResultSet stores the rows returned by the query.
• next() moves the cursor to the next available row and returns false when no rows remain.
• getInt(), getString(), and getDouble() retrieve column values using their column names.
• Try-with-resources closes the ResultSet, Statement, and Connection automatically.

Expected Output

1 | Amit | Java | 85.5
2 | Priya | Python | 91.0
3 | Rahul | SQL | 78.0

The actual output depends on the records stored in the database and the generated ID values.

8. Executing INSERT Queries Using executeUpdate()

The executeUpdate() method is used for SQL statements that modify data or database structures. For INSERT operations, it returns the number of affected rows.

Example: Insert a New Student

String sql =
    "INSERT INTO students (name, course, marks) " +
    "VALUES ('Neha', 'Java', 88.50)";

int rowsAffected = statement.executeUpdate(sql);

System.out.println(
    "Rows inserted: " + rowsAffected
);

Explanation

• The INSERT command adds a new record to the students table.
• The executeUpdate() method sends the command to the database.
• The returned integer indicates how many rows were affected.
• A result of 1 usually indicates that one row was inserted.

Important: The example uses fixed SQL text for learning purposes. Do not concatenate user-provided values into SQL strings. Use PreparedStatement for dynamic values to reduce SQL injection risks.

9. Executing UPDATE Queries Using executeUpdate()

An UPDATE statement modifies existing records in a table. A WHERE clause should normally be included to restrict the records being changed.

Example: Update Student Marks

String sql =
    "UPDATE students SET marks = 95.00 " +
    "WHERE id = 1";

int rowsAffected = statement.executeUpdate(sql);

System.out.println(
    "Rows updated: " + rowsAffected
);

Explanation

• UPDATE identifies the table whose records must be changed.
• SET specifies the new value for the marks column.
• WHERE identifies the student whose record should be updated.
• The returned value indicates how many rows were affected.

If the WHERE clause is omitted, the database may update every row in the table. Always verify the condition before executing an UPDATE statement.

10. Executing DELETE Queries Using executeUpdate()

The DELETE statement removes records from a database table. The WHERE clause specifies which records should be deleted.

Example: Delete a Student Record

String sql =
    "DELETE FROM students WHERE id = 3";

int rowsAffected = statement.executeUpdate(sql);

System.out.println(
    "Rows deleted: " + rowsAffected
);

If a matching row exists, the command typically returns 1. If no row matches the condition, it may return 0.

Safety tip: Before running a DELETE command, test the condition with a SELECT query. Avoid executing DELETE without a WHERE clause unless removing every row is intentional.

11. Understanding the execute() Method

The execute() method is a general-purpose method for executing SQL when the result type may vary or is not known in advance. It returns a boolean indicating whether the first result is a ResultSet.

Example

String sql = "SELECT * FROM students";

boolean hasResultSet = statement.execute(sql);

if (hasResultSet) {
    try (ResultSet resultSet =
             statement.getResultSet()) {
        while (resultSet.next()) {
            System.out.println(
                resultSet.getString("name")
            );
        }
    }
}

When execute() returns true, the first result is a ResultSet. When it returns false, the first result is either an update count or no result. Use getResultSet() or getUpdateCount() as appropriate. Statements that can produce multiple results may require additional result-processing logic.

12. Difference Between executeQuery(), executeUpdate(), and execute()

13. Using WHERE, ORDER BY, and Aggregate Functions

SQL clauses and aggregate functions can be used with Statement to filter, sort, and summarize data.

Example A: Filter Records

String sql =
    "SELECT * FROM students WHERE marks >= 80";

try (ResultSet resultSet =
         statement.executeQuery(sql)) {
    while (resultSet.next()) {
        System.out.println(
            resultSet.getString("name")
        );
    }
}

Example B: Sort Records

String sql =
    "SELECT name, marks FROM students " +
    "ORDER BY marks DESC";

try (ResultSet resultSet =
         statement.executeQuery(sql)) {
    while (resultSet.next()) {
        System.out.println(
            resultSet.getString("name") + " - " +
            resultSet.getDouble("marks")
        );
    }
}

Example C: Calculate Average Marks

String sql =
    "SELECT AVG(marks) AS average_marks " +
    "FROM students";

try (ResultSet resultSet =
         statement.executeQuery(sql)) {
    if (resultSet.next()) {
        double average =
            resultSet.getDouble("average_marks");

        System.out.println(
            "Average marks: " + average
        );
    }
}

Aggregate functions such as COUNT(), SUM(), AVG(), MIN(), and MAX() help generate useful summaries directly in the database.

14. Handling SQL Exceptions

Database operations can fail because of invalid SQL syntax, incorrect credentials, missing tables, network problems, constraint violations, or unavailable database servers. JDBC reports these errors through SQLException and its subclasses.

Example

try (Connection connection =
         DriverManager.getConnection(
             url, username, password);
     Statement statement =
         connection.createStatement()) {

    int rows = statement.executeUpdate(
        "UPDATE students SET marks = 90 WHERE id = 1"
    );

    System.out.println(
        "Rows affected: " + rows
    );

} catch (SQLException e) {
    System.err.println(
        "Database error: " + e.getMessage()
    );
    System.err.println(
        "SQL state: " + e.getSQLState()
    );
    System.err.println(
        "Error code: " + e.getErrorCode()
    );
}

In real applications, use structured logging and handle errors appropriately. Avoid displaying sensitive database details to end users.

15. Resource Management in JDBC

JDBC resources should be closed after use to prevent resource leaks. These resources include Connection, Statement, and ResultSet.

Recommended Approach: Try-with-Resources

try (Connection connection =
         DriverManager.getConnection(
             url, username, password);
     Statement statement =
         connection.createStatement();
     ResultSet resultSet =
         statement.executeQuery(
             "SELECT id, name FROM students"
         )) {

    while (resultSet.next()) {
        System.out.println(
            resultSet.getInt("id") + " " +
            resultSet.getString("name")
        );
    }

} catch (SQLException e) {
    e.printStackTrace();
}

Java automatically closes resources declared in the try-with-resources statement. This approach is generally cleaner and safer than manually calling close() in a finally block.

16. Limitations of the Statement Interface

• SQL injection risk: Dynamically concatenating untrusted input can allow attackers to alter a query.
• Repeated parsing: Repeated execution of similar SQL strings may require additional parsing and processing compared with reusable prepared statements, depending on the driver and database.
• Manual value handling: Developers must correctly format and escape dynamic values if they construct SQL strings, which is error-prone.
• Maintenance difficulties: Long SQL strings created through concatenation can be difficult to read and maintain.
• Limited parameter support: Statement does not provide the same parameter-binding mechanism as PreparedStatement.

17. Statement vs PreparedStatement

Safer Alternative for Dynamic Input

String sql =
    "SELECT * FROM students WHERE course = ?";

try (Connection connection =
         DriverManager.getConnection(
             url, username, password);
     java.sql.PreparedStatement ps =
         connection.prepareStatement(sql)) {

    ps.setString(1, "Java");

    try (ResultSet resultSet = ps.executeQuery()) {
        while (resultSet.next()) {
            System.out.println(
                resultSet.getString("name")
            );
        }
    }

} catch (SQLException e) {
    e.printStackTrace();
}

The question mark represents a parameter placeholder. The setString() method binds the course value separately from the SQL command, reducing the risk that the value will be interpreted as SQL syntax.

18. Common Errors and Troubleshooting

19. Best Practices for Using Statement

• Use Statement primarily for fixed SQL commands that do not require dynamic parameter values.
• Prefer PreparedStatement whenever SQL contains values supplied by users or external systems.
• Use try-with-resources to close JDBC objects automatically.
• Validate database connection settings and ensure the correct JDBC driver is available.
• Always inspect WHERE conditions before executing UPDATE or DELETE statements.
• Use transactions when multiple database changes must succeed or fail together.
• Request only the columns required by the application instead of using SELECT * unnecessarily.
• Use database accounts with only the permissions the application needs.
• Keep passwords and other sensitive configuration outside source code.
• Log useful error information without exposing credentials or confidential data.

20. Practical Project: Student Record Management Using JDBC

Build a small Java console application that performs database operations on a student table. This exercise reinforces creating Statement objects, executing SQL commands, and reading query results.

Project Requirements

• Connect to the college_db database.
• Display all student records.
• Insert a sample student record.
• Update the marks of an existing student.
• Delete a selected student record.
• Display the number of affected rows after each modification.
• Handle SQL exceptions and close resources safely.

Sample Program Structure

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class StudentRecordManager {
    public static void main(String[] args) {
        String url =
            "jdbc:mysql://localhost:3306/college_db";
        String username = "your_username";
        String password = "your_password";

        try (Connection connection =
                 DriverManager.getConnection(
                     url, username, password);
             Statement statement =
                 connection.createStatement()) {

            int inserted = statement.executeUpdate(
                "INSERT INTO students " +
                "(name, course, marks) " +
                "VALUES ('Karan', 'Java', 82.00)"
            );
            System.out.println(
                "Inserted rows: " + inserted
            );

            int updated = statement.executeUpdate(
                "UPDATE students SET marks = 89.00 " +
                "WHERE name = 'Karan' AND course = 'Java'"
            );
            System.out.println(
                "Updated rows: " + updated
            );

            int deleted = statement.executeUpdate(
                "DELETE FROM students " +
                "WHERE name = 'Karan' AND course = 'Java'"
            );
            System.out.println(
                "Deleted rows: " + deleted
            );

        } catch (SQLException e) {
            System.err.println(
                "Database operation failed: " +
                e.getMessage()
            );
        }
    }
}

Note: This demonstration uses fixed sample values and is intended for a practice database. If multiple matching records are possible, use a unique identifier in the WHERE clause. For real applications, use PreparedStatement for dynamic input and add validation, transaction handling, and appropriate authorization.

21. Practice Exercises

1. Create a Statement object and display all records from the students table.
2. Retrieve students whose marks are greater than 80.
3. Display student names in alphabetical order.
4. Insert a new student record and print the number of inserted rows.
5. Update the course of a student using a suitable condition.
6. Delete a practice record and display the affected row count.
7. Use COUNT() to calculate the total number of students.
8. Modify a SELECT query to display only the name and marks columns.
9. Handle a deliberately incorrect table name and inspect the SQLException.
10. Rewrite a query that accepts user input using PreparedStatement.

22. Interview Questions and Answers

Q1. What is Statement in JDBC?

Answer: Statement is an interface in the java.sql package used to execute SQL statements through an established database connection.

Q2. How do you create a Statement object?

Answer: Call connection.createStatement() on a valid JDBC Connection object.

Q3. Which method is used to execute a SELECT query?

Answer: executeQuery() is generally used because SELECT returns a ResultSet.

Q4. What is the purpose of executeUpdate()?

Answer: It executes statements such as INSERT, UPDATE, DELETE, and applicable DDL commands, returning an update count where applicable.

Q5. What does execute() return?

Answer: It returns a boolean indicating whether the first result is a ResultSet.

Q6. What is ResultSet?

Answer: ResultSet represents the tabular data returned by a query and provides methods to move through rows and retrieve column values.

Q7. Why should JDBC resources be closed?

Answer: Closing resources releases database and application resources and helps prevent resource leaks. Try-with-resources automates this process.

Q8. What is the difference between Statement and PreparedStatement?

Answer: Statement executes SQL strings directly, while PreparedStatement supports parameter placeholders and separate value binding. PreparedStatement is generally preferred for dynamic input.

Q9. What is SQL injection?

Answer: SQL injection occurs when untrusted input changes the intended structure of a SQL command. Using PreparedStatement with correctly bound values is a key defense.

Q10. What happens if a DELETE query has no WHERE clause?

Answer: It can delete every row in the target table. The table structure remains unless a separate command removes it.

Q11. What is SQLException?

Answer: SQLException reports database access failures and provides information such as an error message, SQL state, and vendor-specific error code.

Q12. Can Statement execute DDL commands?

Answer: Yes. Statement can execute commands such as CREATE TABLE and ALTER TABLE using an appropriate execution method, commonly executeUpdate().

23. Quick Revision Checklist

• Statement belongs to the java.sql package.
• Create it through Connection.createStatement().
• Use executeQuery() for queries that return a ResultSet.
• Use executeUpdate() for INSERT, UPDATE, DELETE, and applicable DDL statements.
• Use execute() when the result type may vary.
• Use ResultSet.next() to move through query results.
• Use try-with-resources to close JDBC objects.
• A missing WHERE clause can modify or delete every row.
• Avoid concatenating untrusted values into SQL strings.
• Prefer PreparedStatement for parameterized queries.

24. Conclusion

The JDBC Statement interface provides a straightforward way to execute SQL commands from Java applications. By understanding executeQuery(), executeUpdate(), and execute(), developers can retrieve records, modify database data, and perform appropriate database operations.

Reliable JDBC programming also requires careful exception handling, resource management, and SQL security practices. Statement is useful for learning and executing fixed SQL commands, while PreparedStatement is generally the better choice for queries involving dynamic values.

To learn more about enterprise Java development, explore the Advanced Java Training Course at JustAcademy. You can also register for a course demo or visit the JustAcademy website.